/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.webaccess.retailws.DAO;

import com.webaccess.retailws.Bean.ProductBean;
import com.webaccess.retailws.Bean.CategoryBean;
import com.webaccess.retailws.Delegate.CategoryDelegate;
import com.webaccess.retailws.db.DBConnection;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.ArrayList;

/**
 *
 * @author Owner
 */
public class ProductDAO {

    ProductBean prodBean = null;
    CategoryBean categBean = null;
    DBConnection dbc = null;
    Connection conn = null;
    ResultSet rs = null;
    String result = null;
    StringBuffer myQuery = null;
    CategoryDelegate categDelegate = null;
    CommonDAO commDAO = null;

    public ProductDAO() {
        try {
            dbc = new DBConnection();
            //  conn = (Connection) dbc.getConnection();

        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }

    public ProductBean[] getAllProducts() {
        ProductBean[] productList = null;
        prodBean = new ProductBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_selectproduct('0')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    productList = setAllProduct(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return productList;
        }
    }

    private ProductBean[] setAllProduct(ResultSet rs) {
        List alProduct = new ArrayList();
        try {
            alProduct.clear();
            while (rs.next()) {
                prodBean = new ProductBean();
                prodBean.setProductId(rs.getLong("product_id"));
                prodBean.setProductName(rs.getString("productname"));
                prodBean.setProductBrand(rs.getString("productbrand"));
                prodBean.setProductBrandId(rs.getLong("productbrand_id"));
                prodBean.setProductCategoryId(rs.getLong("productcategory_id"));
                prodBean.setProductCategory(rs.getString("productcategory"));
                prodBean.setProductDescription(rs.getString("description"));
                prodBean.setProductDiscount(rs.getString("discount"));
                prodBean.setProductLargeImageName(rs.getString("prodlargeimage"));
                prodBean.setProductSmallImageName(rs.getString("prodsmallimage"));
                prodBean.setProductPrice(rs.getString("price"));
                prodBean.setProductQty(rs.getString("quantity"));
                prodBean.setProductStatus(rs.getString("status"));
                prodBean.setProductStatusId(rs.getLong("status_id"));
                prodBean.setProductTitle(rs.getString("producttitle"));
                prodBean.setProductTaxValue(rs.getString("tax"));
                prodBean.setProductTaxable(rs.getInt("taxable"));
                alProduct.add(prodBean);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return (ProductBean[]) alProduct.toArray(new ProductBean[0]);
        }
    }

    public ProductBean getProduct(Long productId) {
        prodBean = new ProductBean();

        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_selectproduct('");
            myQuery.append(productId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    prodBean = setProduct(rs);
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return prodBean;
        }
    }

    private ProductBean setProduct(ResultSet rs) {
        try {
            while (rs.next()) {
                prodBean = new ProductBean();
                prodBean.setProductId(rs.getLong("product_id"));
                prodBean.setProductName(rs.getString("productname"));
                prodBean.setProductBrand(rs.getString("productbrand"));
                prodBean.setProductBrandId(rs.getLong("productbrand_id"));
                prodBean.setProductCategoryId(rs.getLong("productcategory_id"));
                prodBean.setProductCategory(rs.getString("productcategory"));
                prodBean.setProductDescription(rs.getString("description"));
                prodBean.setProductDiscount(rs.getString("discount"));
                prodBean.setProductLargeImageName(rs.getString("prodlargeimage"));
                prodBean.setProductSmallImageName(rs.getString("prodsmallimage"));
                prodBean.setProductPrice(rs.getString("price"));
                prodBean.setProductQty(rs.getString("quantity"));
                prodBean.setProductStatus(rs.getString("status"));
                prodBean.setProductStatusId(rs.getLong("status_id"));
                prodBean.setProductTitle(rs.getString("producttitle"));
                prodBean.setProductTaxValue(rs.getString("tax"));
                prodBean.setProductTaxable(rs.getInt("taxable"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return prodBean;
        }
    }

    public boolean saveProduct(ProductBean prodBean) {
        boolean isProductSaved = false;
        Long productId = 0L;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            if (prodBean.getProductId() == null) {
                myQuery.append("CALL sp_saveproduct(NULL,'");
            } else {
                myQuery.append("CALL sp_saveproduct('");
                myQuery.append(prodBean.getProductId());
                myQuery.append("','");
            }
            myQuery.append(prodBean.getProductName());
            myQuery.append("','");
            myQuery.append(prodBean.getProductDescription());
            myQuery.append("','");
            myQuery.append(prodBean.getProductSmallImageName());
            myQuery.append("','");
            myQuery.append(prodBean.getProductPrice());
            myQuery.append("','");
            myQuery.append(prodBean.getProductBrandId());
            myQuery.append("','");
            myQuery.append(prodBean.getProductDiscount());
            myQuery.append("','");
            myQuery.append(prodBean.getProductQty());
            myQuery.append("','");
            myQuery.append(prodBean.getProductTaxable());
            myQuery.append("','");
            myQuery.append(prodBean.getProductStatusId());
            myQuery.append("','");
            myQuery.append(prodBean.getProductLargeImageName());
            myQuery.append("','");
            myQuery.append(prodBean.getProductTitle());
            myQuery.append("','");
            myQuery.append(prodBean.getProductCategoryId());
            myQuery.append("','");
            myQuery.append(prodBean.getProductTaxValue());
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (prodBean.getProductId() == null) {
                if (rs != null) {
                    String rsValue = String.valueOf(rs);
                    if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                        while (rs.next()) {
                            productId = rs.getLong("sp_ProductId");
                        }
                    }
                }
                if (productId > 0) {
                    isProductSaved = true;
                }
            } else {
                isProductSaved = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return isProductSaved;
        }
    }

    public boolean deleteProduct(Long productId) {
        boolean isProductDeleted = false;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_deleteproduct('");
            myQuery.append(productId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                isProductDeleted = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return isProductDeleted;
        }
    }

    public ProductBean[] searchProduct(String product, String productName, Long productStatusId, Long productCategoryId, Long productBrandId) {
        ProductBean[] productList = null;
        prodBean = new ProductBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_searchProduct(");
            if (!product.equalsIgnoreCase("")) {
                myQuery.append("'");
                myQuery.append(product);
                myQuery.append("%',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)");
            } else if (!productName.equalsIgnoreCase("")) {
                myQuery.append("NULL,'");
                myQuery.append(productName);
                myQuery.append("%',NULL,NULL,NULL,NULL,NULL,NULL,NULL)");
            } else if (productStatusId > 0) {
                myQuery.append("NULL,NULL,'");
                myQuery.append(productStatusId);
                myQuery.append("',NULL,NULL,NULL,NULL,NULL,NULL)");
            } else if (productCategoryId > 0) {
                myQuery.append("NULL,NULL,NULL,'");
                myQuery.append(productCategoryId);
                myQuery.append("',NULL,NULL,NULL,NULL,NULL)");
            } else if (productBrandId > 0) {
                myQuery.append("NULL,NULL,NULL,NULL,'");
                myQuery.append(productBrandId);
                myQuery.append("',NULL,NULL,NULL,NULL)");
            }
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    productList = setAllProduct(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return productList;
        }
    }

    public ProductBean[] getProductByCategory(Long categoryId) {
        categDelegate = new CategoryDelegate();
        ProductBean[] productList = null;
        prodBean = new ProductBean();
        List alProdList = new ArrayList();
        rs = null;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            CategoryBean[] categList = categDelegate.searchCategory(categoryId);

            if (categList != null) {
                for (int i = 0; i < categList.length - 1; i++) {
                    myQuery = new StringBuffer();
                    myQuery.append("CALL sp_searchProduct(");
                    myQuery.append("NULL,NULL,NULL,'");
                    myQuery.append(categList[i].getCategoryId());
                    myQuery.append("',NULL,NULL,NULL,NULL,NULL)");
                    rs = dbc.executeProcedure(conn, myQuery.toString());
                    if (rs != null) {
                        String rsValue = String.valueOf(rs);
                        if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                            alProdList = setProductByCategory(rs);
                        }
                    }
                }
            } else {
                myQuery = new StringBuffer();
                myQuery.append("CALL sp_searchProduct(");
                myQuery.append("NULL,NULL,NULL,'");
                myQuery.append(categoryId);
                myQuery.append("',NULL,NULL,NULL,NULL,NULL)");
                rs = dbc.executeProcedure(conn, myQuery.toString());
                if (rs != null) {
                    String rsValue = String.valueOf(rs);
                    if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                        alProdList = setProductByCategory(rs);
                    }
                }
            }
            productList = (ProductBean[]) alProdList.toArray(new ProductBean[0]);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return productList;
        }
    }

    private List setProductByCategory(ResultSet rs) {
        List alProduct = new ArrayList();
        try {
            alProduct.clear();
            while (rs.next()) {
                prodBean = new ProductBean();
                prodBean.setProductId(rs.getLong("product_id"));
                prodBean.setProductName(rs.getString("productname"));
                prodBean.setProductBrand(rs.getString("productbrand"));
                prodBean.setProductBrandId(rs.getLong("productbrand_id"));
                prodBean.setProductCategoryId(rs.getLong("productcategory_id"));
                prodBean.setProductCategory(rs.getString("productcategory"));
                prodBean.setProductDescription(rs.getString("description"));
                prodBean.setProductDiscount(rs.getString("discount"));
                prodBean.setProductLargeImageName(rs.getString("prodlargeimage"));
                prodBean.setProductSmallImageName(rs.getString("prodsmallimage"));
                prodBean.setProductPrice(rs.getString("price"));
                prodBean.setProductQty(rs.getString("quantity"));
                prodBean.setProductStatus(rs.getString("status"));
                prodBean.setProductStatusId(rs.getLong("status_id"));
                prodBean.setProductTitle(rs.getString("producttitle"));
                prodBean.setProductTaxValue(rs.getString("tax"));
                prodBean.setProductTaxable(rs.getInt("taxable"));
                alProduct.add(prodBean);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return alProduct;
        }
    }

    public ProductBean[] getProductByPrice(Long min, Long max) {
        ProductBean[] productList = null;
        prodBean = new ProductBean();
        List alProdList = new ArrayList();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            alProdList.clear();
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_searchProduct(");
            myQuery.append("NULL,NULL,NULL,NULL,NULL,'");
            myQuery.append(min);
            myQuery.append("','");
            myQuery.append(max);
            myQuery.append("',NULL,NULL)");

            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    productList = setProductByPrice(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return productList;
        }
    }

    private ProductBean[] setProductByPrice(ResultSet rs) {
        return setAllProduct(rs);
    }

    public ProductBean[] getAllCartProduct(Long cartId) {
        ProductBean[] cartProductList = null;
        prodBean = new ProductBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_selectcartproduct('");
            myQuery.append(cartId);
            myQuery.append("',NULL)");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    cartProductList = setAllCartProduct(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return cartProductList;
        }
    }

    private ProductBean[] setAllCartProduct(ResultSet rs) {
        List alProduct = new ArrayList();
        try {
            alProduct.clear();
            while (rs.next()) {
                prodBean = new ProductBean();
                prodBean.setProductId(rs.getLong("product_id"));
                prodBean.setProductName(rs.getString("productname"));
                prodBean.setProductBrandId(rs.getLong("brand_id"));
                prodBean.setProductDescription(rs.getString("description"));
                prodBean.setProductDiscount(rs.getString("discount"));
                prodBean.setProductLargeImageName(rs.getString("prodlargeimage"));
                prodBean.setProductSmallImageName(rs.getString("prodsmallimage"));
                prodBean.setProductPrice(rs.getString("price"));
                prodBean.setProductQty(rs.getString("quantity"));
                prodBean.setProductStatusId(rs.getLong("status_id"));
                prodBean.setProductTitle(rs.getString("producttitle"));
                prodBean.setProductTaxable(rs.getInt("taxable"));
                prodBean.setAddToCartQty(rs.getString("cartproductqty"));
                prodBean.setCartId(rs.getLong("cart_id"));
                prodBean.setCartProductId(rs.getLong("cartproduct_id"));
                prodBean.setCartProductCost(rs.getString("cartproductcost"));
                prodBean.setProductSubTotal(String.valueOf(rs.getLong("cartproductqty") * rs.getLong("price")));
                alProduct.add(prodBean);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return (ProductBean[]) alProduct.toArray(new ProductBean[0]);
        }
    }

    public ProductBean addToCart(ProductBean prodBean, Long userId) {
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            if (prodBean.getCartId() == 0) {
                myQuery.append("CALL sp_addtocart(NULL,'");
            } else {
                myQuery.append("CALL sp_addtocart('");
                myQuery.append(prodBean.getCartId());
                myQuery.append("','");
            }
            myQuery.append(userId);
            myQuery.append("','");
            myQuery.append(prodBean.getProductId());
            myQuery.append("','1','");
            myQuery.append(prodBean.getAddToCartQty());
            myQuery.append("','");
            myQuery.append(prodBean.getAddToCartProductDate());
            myQuery.append("','");
            myQuery.append(prodBean.getCartProductCost());
            myQuery.append("','");
            myQuery.append(prodBean.getCartCost());
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    while (rs.next()) {
                        if (prodBean.getCartId() == 0) {
                            prodBean.setCartId(rs.getLong("sp_CartId"));
                        } else {
                            prodBean.setCartProductId(rs.getLong("sp_CartProductId"));
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return prodBean;
        }
    }

    public Long getCartId(Long userId, Long orderId) {
        Long cartId = 0L;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            if (userId != null && userId > 0) {
                myQuery = new StringBuffer();
                myQuery.append("SELECT 	`cart_id` FROM `retailws`.`cart` WHERE `user_id` ='");
                myQuery.append(userId);
                myQuery.append("' AND `cartstatus` = '1' AND `datastatus_id` != '3' ");
            }else if(orderId != null && orderId > 0 ){
                myQuery = new StringBuffer();
                myQuery.append("SELECT 	`cart_id`	FROM 	`retailws`.`order`  WHERE `order_id` ='");
                myQuery.append(orderId);
                myQuery.append("' AND `datastatus_id` != '3'");
            }

            rs = dbc.executeQuery(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    while (rs.next()) {
                        cartId = rs.getLong("cart_id");
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return cartId;
        }
    }

    public boolean removeProductFromCart(Long cartProductId, Long cartId, Long cartCost) {
        boolean isCartUpdated = false;
        rs = null;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_deleteproductfromcart('");
            myQuery.append(cartProductId);
            myQuery.append("','");
            myQuery.append(cartId);
            myQuery.append("','");
            myQuery.append(cartCost);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                isCartUpdated = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return isCartUpdated;
        }
    }

    public boolean clearCart(Long cartId) {
        boolean isCartRemoved = false;
        rs = null;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_deletecart('");
            myQuery.append(cartId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                isCartRemoved = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return isCartRemoved;
        }
    }

    public ProductBean[] getSearchProducts(Long categoryId, String searchProduct) {
        ProductBean[] productList = null;
        List alProdList = new ArrayList();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            alProdList.clear();
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_searchProduct(");
            myQuery.append("NULL,NULL,NULL,NULL,NULL,NULL,NULL,'%");
            myQuery.append(searchProduct);
            myQuery.append("%','");
            myQuery.append(categoryId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    productList = setSearchProducts(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return productList;
        }
    }

    private ProductBean[] setSearchProducts(ResultSet rs) {
        return setAllProduct(rs);
    }

    public LinkedHashMap getProductByCategoryLHMap(Long categoryId) {
        LinkedHashMap tempLHMap = new LinkedHashMap();
        LinkedHashMap productByCategoryLHMap = new LinkedHashMap();
        categDelegate = new CategoryDelegate();
        commDAO = new CommonDAO();

        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            CategoryBean[] categList = categDelegate.searchCategory(categoryId);

            if (categList != null) {
                for (int i = 0; i < categList.length - 1; i++) {
                    myQuery = new StringBuffer();
                    myQuery.append("CALL sp_searchProduct(");
                    myQuery.append("NULL,NULL,NULL,'");
                    myQuery.append(categList[i].getCategoryId());
                    myQuery.append("',NULL,NULL,NULL,NULL,NULL)");
                    tempLHMap = commDAO.getData(myQuery.toString());
                    productByCategoryLHMap.putAll(tempLHMap);
                }
            } else {
                myQuery = new StringBuffer();
                myQuery.append("CALL sp_searchProduct(");
                myQuery.append("NULL,NULL,NULL,'");
                myQuery.append(categoryId);
                myQuery.append("',NULL,NULL,NULL,NULL,NULL)");
                productByCategoryLHMap = commDAO.getData(myQuery.toString());
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return productByCategoryLHMap;
        }
    }

    public ProductBean getCartProduct(Long cartProductId) {
        prodBean = new ProductBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_selectcartproduct(NULL,'");
            myQuery.append(cartProductId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    prodBean = setCartProduct(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return prodBean;
        }
    }

    private ProductBean setCartProduct(ResultSet rs) {
        try {
            while (rs.next()) {
                prodBean = new ProductBean();
                prodBean.setProductId(rs.getLong("product_id"));
                prodBean.setProductName(rs.getString("productname"));
                prodBean.setProductBrandId(rs.getLong("brand_id"));
                prodBean.setProductDescription(rs.getString("description"));
                prodBean.setProductDiscount(rs.getString("discount"));
                prodBean.setProductLargeImageName(rs.getString("prodlargeimage"));
                prodBean.setProductSmallImageName(rs.getString("prodsmallimage"));
                prodBean.setProductPrice(rs.getString("price"));
                prodBean.setProductQty(rs.getString("quantity"));
                prodBean.setProductStatusId(rs.getLong("status_id"));
                prodBean.setProductTitle(rs.getString("producttitle"));
                prodBean.setProductTaxable(rs.getInt("taxable"));
                prodBean.setAddToCartQty(rs.getString("cartproductqty"));
                prodBean.setCartId(rs.getLong("cart_id"));
                prodBean.setCartProductId(rs.getLong("cartproduct_id"));
                prodBean.setCartProductCost(rs.getString("cartproductcost"));
                prodBean.setCartCost(rs.getString("cartcost"));
                prodBean.setProductSubTotal(String.valueOf(rs.getLong("cartproductqty") * rs.getLong("price")));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return prodBean;
        }
    }

    public boolean updateCartProduct(ProductBean prodBean) {
        boolean isCartProductUpdated = false;
        rs = null;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_updatecartproduct('");
            myQuery.append(prodBean.getCartId());
            myQuery.append("','");
            myQuery.append(prodBean.getProductId());
            myQuery.append("','");
            myQuery.append(prodBean.getAddToCartQty());
            myQuery.append("','");
            myQuery.append(prodBean.getAddToCartProductDate());
            myQuery.append("','");
            myQuery.append(prodBean.getCartProductCost());
            myQuery.append("','");
            myQuery.append(prodBean.getCartCost());
            myQuery.append("','");
            myQuery.append(prodBean.getCartProductId());
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                isCartProductUpdated = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return isCartProductUpdated;
        }

    }
}
